--------------------------------------------------------
--  File created - Friday-November-20-2015   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Function FN_CM_BOX2SET
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "FN_CM_BOX2SET" ( box_no varchar2, site_code varchar2)
RETURN VARCHAR2 IS

v_prefix        varchar2(5);
v_sitecode      varchar2(5);
v_serial        varchar2(10);
v_lot_set       varchar2(15);

v_n1            number;
v_n2            number;
v_n3            number;
v_n4            number;
v_n5            number;
v_n6            number;
v_n7            number;
v_n8            number;

v_pw1           varchar2(1);
v_pw2           varchar2(1);


BEGIN
  IF  box_no  IS NULL THEN
      Return NULL;
  END IF;
  IF LENGTH(box_no) = 12 THEN
       /* LG Standard Labels (do next step) */
      RETURN box_no;
  END IF;

  
  v_prefix := substr(box_no,0,3);
  v_serial := substr(box_no,4,5);

 ---------------------------------------------------------------------------------------
   --Step 1
   ---------------------------------------------------------------------------------------
   v_n1 := to_number(
            v_prefix ||
            fn_cm_pw_code('3',substr(v_serial,1,1)) ||
            fn_cm_pw_code('3',substr(v_serial,2,1)) ||
            fn_cm_pw_code('3',substr(v_serial,3,1)) ||
            fn_cm_pw_code('3',substr(v_serial,4,1)) ||
            fn_cm_pw_code('3',substr(v_serial,5,1))
            );
   v_n2 := v_n1/24;
   v_n3 := trunc(v_n2)*24;
   v_n4 := v_n1 - v_n3;

   v_pw1 := fn_cm_pw_code('1',to_char(v_n4));

   ---------------------------------------------------------------------------------------
   --Step2
   ---------------------------------------------------------------------------------------
  v_n5 := to_number(fn_cm_pw_code('3',substr(site_code,1,1)));
  v_n6 := to_number(fn_cm_pw_code('3',substr(site_code,2,1)));
  --v_n7 := v_n1 - ( v_n5 * v_n6 * 659 );
  v_n7 := abs(v_n1 - ( v_n5 * v_n6 * 659 ));
  v_n8 := v_n7 - ( trunc(v_n7/24)*24);

  v_pw2 := fn_cm_pw_code('2',to_char(v_n8));

  v_lot_set := v_prefix || site_code || v_pw1 || v_pw2 || v_serial;

  return v_lot_set;

EXCEPTION
WHEN OTHERS THEN
    return null;
END FN_CM_BOX2SET;

/
--------------------------------------------------------
--  DDL for Function FN_CM_CK_EDI_HIST
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "FN_CM_CK_EDI_HIST" (f_item_id varchar2, f_filename varchar2, f_filesize varchar2)
RETURN VARCHAR2 IS

v_result varchar2(1);

BEGIN
  select 'Y' 
  into   v_result
  from   tb_edi_hist
  where  edi_filename = f_filename
  and    edi_filesize = f_filesize
  and    item_id      = f_item_id;
  
  IF v_result='Y' THEN
    return 'F';
  ELSE
    return 'T';
  END IF;    

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    return 'T';
  WHEN OTHERS THEN
    return 'F';
END FN_CM_CK_EDI_HIST;

/
--------------------------------------------------------
--  DDL for Function FN_CM_GET_SITECODE
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "FN_CM_GET_SITECODE" (sn_no varchar2, prod_cat varchar2)
RETURN VARCHAR2 IS

v_prefix        varchar2(5);
v_sitecode      varchar2(5);
v_serial        varchar2(10);
v_lot_set       varchar2(15);

v_n1            number;
v_n2            number;
v_n3            number;
v_n4            number;
v_n5            number;
v_n6            number;
v_n7            number;
v_n8            number;

v_pw1           varchar2(1);
v_pw2           varchar2(1);


BEGIN


EXCEPTION
WHEN OTHERS THEN
    return null;
END FN_CM_GET_SITECODE;

/
--------------------------------------------------------
--  DDL for Function FN_CM_PW_CODE
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "FN_CM_PW_CODE" 
(
    in_class                     in    varchar2
 ,  in_value                     in    varchar2
)
RETURN varchar2
IS
/**************************************************************************************************
*  Function Name  :  FN_CM_PW_CODE
*  Purpose        :  brand pw number? ??
*
*  Modification   : Date        Se              Description
*                   ----------  ---------       -------------------------------------------------
*                   2005/03/07  gunhlee         Initial Release...
**************************************************************************************************/

  v_result                   varchar2(10);

BEGIN

    v_result     :=    '';

    BEGIN
        if in_class = '1' then

           select decode(in_value, '0', 'L',
                                   '1', 'T',
                                   '2', 'D',
                                   '3', 'M',
                                   '4', 'E',
                                   '5', 'K',
                                   '6', 'U',
                                   '7', 'N',
                                   '8', 'A',
                                   '9', 'W',
                                   '10', 'X',
                                   '11', 'F',
                                   '12', 'J',
                                   '13', 'B',
                                   '14', 'R',
                                   '15', 'V',
                                   '16', 'P',
                                   '17', 'H',
                                   '18', 'Y',
                                   '19', 'C',
                                   '20', 'Q',
                                   '21', 'Z',
                                   '22', 'S',
                                   '23', 'G',
                                   '')
           into   v_result
           from   dual;

        elsif in_class = '2' then
           select decode(in_value, '0', 'H',
                                   '1', 'B',
                                   '2', 'G',
                                   '3', 'R',
                                   '4', 'A',
                                   '5', 'J',
                                   '6', 'K',
                                   '7', 'L',
                                   '8', 'S',
                                   '9', 'C',
                                   '10', 'M',
                                   '11', 'T',
                                   '12', 'Z',
                                   '13', 'D',
                                   '14', 'N',
                                   '15', 'U',
                                   '16', 'Y',
                                   '17', 'E',
                                   '18', 'Q',
                                   '19', 'V',
                                   '20', 'X',
                                   '21', 'P',
                                   '22', 'F',
                                   '23', 'W',
                                   '')
           into   v_result
           from   dual;
        elsif in_class = '3' then
           select decode(in_value, 'A', '25',
                                   'B', '32',
                                   'C', '24',
                                   'D', '16',
                                   'E', '31',
                                   'F', '10',
                                   'G', '26',
                                   'H', '35',
                                   'I', '23',
                                   'J', '30',
                                   'K', '21',
                                   'L', '34',
                                   'M', '22',
                                   'N', '17',
                                   'O', '18',
                                   'P', '33',
                                   'Q', '14',
                                   'R', '19',
                                   'S', '29',
                                   'T', '13',
                                   'U', '28',
                                   'V', '12',
                                   'W', '27',
                                   'X', '20',
                                   'Y', '15',
                                   'Z', '11',
                                   '0', '0',
                                   '1', '1',
                                   '2', '2',
                                   '3', '3',
                                   '4', '4',
                                   '5', '5',
                                   '6', '6',
                                   '7', '7',
                                   '8', '8',
                                   '9', '9',
                                   '')
           into   v_result
           from   dual;
        else
            v_result     := '';
        end if;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
             v_result    :=    '';
        WHEN OTHERS THEN
             v_result    :=    '';
    END;


    RETURN v_result;

END FN_CM_PW_CODE;

/
--------------------------------------------------------
--  DDL for Function FN_SN_VAL
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "FN_SN_VAL" (f_serial_no varchar2)
return boolean is
  /*v_sn_ym varchar2(3);
  v_sn_ss varchar2(4);
  v_sn_seq varchar(6);*/
  --/v_cnt    number(2);
  
  v_result boolean;
begin
  v_result :=true;
  
  if LENGTH(f_serial_no) not in (10,11,12,13,14) then
    v_result:=false;
  end if;
  /*v_sn_ym := substr(serial_no,0,3);
  v_sn_ss := substr(serial_no,4,4);
  v_sn_seq :=substr(serial_no,8,length(serial_no)-7);*/
    
  for i_cnt in 0 .. length(f_serial_no)
    loop
    if   SUBSTR(f_serial_no,i_cnt,1) not in ('0','1','2','3','4','5','6','7','8','9',
               'Q','W','E','R','T','Y','U','I','O','P','A','S','D','F','G','H','J',
               'K','L','Z','X','C,','V','B','N','M')  then
          v_result:=false;
    end if;
  end loop;
  
  /*for i_cnt in 0 .. length(v_sn_seq)
    loop
    if to_number(substr(v_sn_seq,i_cnt,1)) not in (0,1,2,3,4,5,6,7,8,9) then
          b_result:=false;
    end if;
  end loop;
  for i_cnt in 0 .. length(v_sn_ss)
    loop
    if substr(v_sn_ss,i_cnt,1) not in 
              ('Q','W','E','R','T','Y','U','I','O','P','A','S','D','F','G','H','J',
               'K','L','Z','X','C,','V','B','N','M') then
          b_result:=false;
    end if;
  end loop;
  */
  return(v_result);
exception
  when VALUE_ERROR then
      return false;
  when others then
      dbms_output.put_line (f_serial_no);    
end FN_SN_VAL;

/
